package com.jysz.certificate.persistence.dao.mf;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.type.DoubleType;
import org.hibernate.type.IntegerType;
import org.hibernate.type.StringType;
import org.springframework.stereotype.Repository;

import com.jysz.certificate.persistence.dao.AbstractBaseDao;
import com.jysz.certificate.persistence.enums.mf.BillStatus;
import com.jysz.certificate.persistence.model.mf.BillOption;
import com.jysz.certificate.persistence.model.system.Shop;
import com.jysz.certificate.utils.DateUtil;

/**
 * 发票费用明细DAO
 * @author xuxiaolong
 */
@Repository
public class BillOptionDao extends AbstractBaseDao<BillOption, Long> {
	
	public List<BillOption>  findByApplyId(Long applyId){
		Criteria c=createCriteria();
		c.setProjection(Projections.groupProperty("financial"));
		
		return c.list();
	}
	
	/**
	 * 获得指定店铺、金融机构的预收月份
	 * @param financialId
	 * @param shopId
	 * @return
	 */
	public List<String> findAdvanceMonths(Long financialId,Long shopId){
		
		String currentMonth=DateUtil.formatDate(new Date(), "yyyy-MM");
		Query q=getSession().createQuery("select month from BillOption bo where bo.financial.id=:finId and bo.shop.id=:shopId and bo.month>:cmonth");
		q.setLong("finId", financialId);
		q.setLong("shopId", shopId);
		q.setString("cmonth", currentMonth);
		return q.list();
		
	}
	
	/**
	 * 获得指定店铺、金融机构的指定时间段已经收过的月份
	 * @param financialId
	 * @param shopId
	 * @return
	 */
	public List<String> findAdvanceMonths(Long financialId,Long shopId,String start,String end){
		
		Query q=getSession().createQuery("select month from BillOption bo where bo.financial.id=:finId and bo.shop.id=:shopId and bo.month>=:start and bo.month<=:end and bo.status not in (:statuses) ");
		q.setLong("finId", financialId);
		q.setLong("shopId", shopId);
		q.setString("start", start);
		q.setString("end", end);
		List<BillStatus> list=new ArrayList<BillStatus>();
		list.add(BillStatus.INVALID);
		list.add(BillStatus.CANCLEREDREOPEN);//作废和注销重开的发票需要重新生成，所以不查询出来
		q.setParameterList("statuses",list);
		return q.list();
		
	}
	
	/**
	 * 获得指定店铺、金融机构、月份的发票明细
	 * @param financialId
	 * @param shopId
	 * @return
	 */
	public List<BillOption> findAdvanceMonths(Long financialId,Long shopId,String month){
		
		Query q=getSession().createQuery("from BillOption bo where bo.financial.id=:finId and bo.shop.id=:shopId and bo.month=:cmonth");
		q.setLong("finId", financialId);
		q.setLong("shopId", shopId);
		q.setString("cmonth", month);
		return q.list();
		
	}
	
	/**
	 * 获得指定店铺、金融机构 的所有预收发票明细
	 * @param financialId
	 * @param shopId
	 * @return
	 */
	public List<BillOption> findAdvanceOption(Long financialId,Long shopId){
		String currentMonth=DateUtil.formatDate(new Date(), "yyyy-MM");
		Query q=getSession().createQuery("from BillOption bo where bo.financial.id=:finId and bo.shop.id=:shopId and bo.month<:cmonth and bo.advance=:advance");
		q.setLong("finId", financialId);
		q.setLong("shopId", shopId);
		q.setString("cmonth", currentMonth);
		q.setBoolean("advance", true);
		return q.list();
		
	}
	
	/**
	 * 获得指定店铺、指定月份的预收发票明细
	 * @param shopId
	 * @param month yyyy-MM
	 * @return
	 */
	public List<BillOption> findAdvanceOption(Long shopId,String month){
		
		Criteria c=createCriteria();
		c.add(Restrictions.eq("month", month));
		c.add(Restrictions.eq("advance", true));
		c.createCriteria("financial").add(Restrictions.eq("billing", "1"));
		c.createCriteria("shop").add(Restrictions.eq("id", shopId));
		
		return c.list();
		
	}
	
	/**
	 * 获得指定店铺、指定月份的预收发票明细
	 * @param shopId
	 * @param month yyyy-MM
	 * @return
	 */
	public List<BillOption> findAdvanceOption(Long finId,String month,Long billId){
		
		Criteria c=createCriteria();
		c.add(Restrictions.eq("month", month));
		c.add(Restrictions.eq("advance", true));
		Criteria finC=c.createCriteria("financial");
		finC.add(Restrictions.eq("id", finId));
		finC.add(Restrictions.eq("billing", "0"));
		c.createCriteria("bills").add(Restrictions.ne("id", billId));
		return c.list();
		
	}
	
	/**
	 * 更改申请单中发票明细状态
	 * @param applyId
	 * @param status
	 */
	public void changeStatus(Long applyId,BillStatus status){
		StringBuilder sb=new StringBuilder("UPDATE mf_bill_option_ bo ,mf_bill_ bill ,mf_bill_apply_ apply ,rs_mf_bill_option_ rs SET bo._status=:status ");
		sb.append(" WHERE apply.id=bill._bill_apply_id AND bill.id=rs._bill_id AND bo.id=rs._option_id ");
		sb.append(" AND apply.id=:applyId ");
		SQLQuery q=getSession().createSQLQuery(sb.toString());
		q.setInteger("status", status.getIndex());
		
		q.setLong("applyId", applyId);
		q.executeUpdate();
	}
	
	/**
	 * 获得年度总收入,用于图形报表
	 * @author xuxiaolong
	 * @param year
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public Map<Integer,Double> findIncomeTotal(Integer year){
		String startMonth=year.toString()+"-01";
		String endMonth=year.toString()+"-12";
		String sql="SELECT SUBSTRING(m._month,6,2)*1 AS mon , SUM(m._amount) AS amount  FROM mf_bill_option_ m WHERE m._month >=:startMonth AND m._month<=:endMonth AND (m._status=2 OR m._status=7) GROUP BY m._month ";
		
		SQLQuery q=getSession().createSQLQuery(sql);
		q.addScalar("mon", IntegerType.INSTANCE);
		q.addScalar("amount", DoubleType.INSTANCE);
		q.setString("startMonth", startMonth);
		q.setString("endMonth", endMonth);
		List<Object[]> list=q.list();

		Map<Integer,Double> rus=new HashMap<Integer, Double>();
		for(Object[] ary:list){
			if(ary.length==2){
				rus.put((Integer)ary[0], (Double)ary[1]);
			}
		}
		
		return rus;
	}
	
	/**
	 * 获得年度金融机构收入分配,用于图形报表
	 * @author xuxiaolong
	 * @param year
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public Map<String,Double> findIncomeFin(Integer year){
		String startMonth=year.toString()+"-01";
		String endMonth=year.toString()+"-12";
		
		String sql="SELECT f._brief AS fin , SUM(m._amount) AS amount   FROM mf_bill_option_  m ,sys_financial_ f WHERE m._financial_id=f.id AND m._month >=:startMonth AND m._month<=:endMonth AND (m._status=2 OR m._status=7)  GROUP BY m._financial_id";
		
		SQLQuery q=getSession().createSQLQuery(sql);
		q.addScalar("fin", StringType.INSTANCE);
		q.addScalar("amount", DoubleType.INSTANCE);
		q.setString("startMonth", startMonth);
		q.setString("endMonth", endMonth);
		List<Object[]> list=q.list();

		Map<String,Double> rus=new HashMap<String, Double>();
		for(Object[] ary:list){
			if(ary.length==2){
				rus.put((String)ary[0], (Double)ary[1]);
			}
		}
		
		return rus;
	}
	
	
	/**
	 * 按金融机构统计业务往来的店铺
	 * @author xuxiaolong
	 * @param year
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public LinkedHashMap<String,Integer> findFinShop(Date startDate,Date endDate){
		String startMonth=DateUtil.formatDate(startDate, "yyyy-MM");
		String endMonth=DateUtil.formatDate(endDate, "yyyy-MM");
		
		StringBuilder sb = new StringBuilder("SELECT f._brief AS fin ,COUNT(*) AS counts FROM ( ");
	sb.append(" SELECT o._financial_id AS fin ,o._shop_id AS shop,COUNT(*) FROM mf_bill_option_ o ");
	sb.append(" WHERE 1=1 AND (o._status=2 OR o._status=7) ");
	sb.append(" AND o._month>=:startMonth AND o._month <=:endMonth AND o._amount >0 ");
	sb.append(" GROUP BY o._financial_id ,o._shop_id ");
	sb.append(" ) t ,sys_financial_ f WHERE t.fin=f.id  GROUP BY fin order by counts desc ");

		
		SQLQuery q=getSession().createSQLQuery(sb.toString());
		q.addScalar("fin", StringType.INSTANCE);
		q.addScalar("counts", IntegerType.INSTANCE);
		q.setString("startMonth", startMonth);
		q.setString("endMonth", endMonth);
		List<Object[]> list=q.list();
		
		LinkedHashMap<String,Integer> rus=new LinkedHashMap<String, Integer>();
		for(Object[] ary:list){
			if(ary.length==2){
				rus.put((String)ary[0], (Integer)ary[1]);
			}
		}
		
		return rus;
	}

	/**
	 * @param monthDate
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Shop> findShopIdsAtBeforeMonthOfStateCharged(String monthDate) {
		Criteria criteria = this.createCriteria();
		criteria.setProjection(Projections.projectionList().add(Projections.alias(Projections.groupProperty("shop"), "shop")));
		criteria.add(Restrictions.le("month", monthDate));
		criteria.add(Restrictions.disjunction().add(Restrictions.isNull("hasAssigned")).add(Restrictions.eq("hasAssigned", false)));
		
		Criteria billsCriteria = criteria.createCriteria("bills");
		billsCriteria.add(Restrictions.eq("status", BillStatus.FULLCHARGE));
		
//		criteria.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
		
		return criteria.list();
	}

	@SuppressWarnings("unchecked")
	public List<BillOption> findBillOptionsAtMonthOfStateCharged(
			String monthDate, Shop shop) {
		Criteria criteria = this.createCriteria();
//		criteria.setProjection(Projections.projectionList().add(Projections.alias(Projections.groupProperty("month"), "monthDate")));
		criteria.add(Restrictions.eq("month", monthDate));
		criteria.add(Restrictions.disjunction().add(Restrictions.isNull("hasAssigned")).add(Restrictions.eq("hasAssigned", false)));
		criteria.add(Restrictions.eq("shop", shop));
		Criteria billsCriteria = criteria.createCriteria("bills");
		billsCriteria.add(Restrictions.eq("status", BillStatus.FULLCHARGE));
		
//		criteria.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
		
		return criteria.list();
	}

	@SuppressWarnings("unchecked")
	public List<String> findShopMonthsAtBeforeMonthOfStateCharged(
			String monthDate, Shop shop) {
		Criteria criteria = this.createCriteria();
		criteria.setProjection(Projections.projectionList().add(Projections.alias(Projections.groupProperty("month"), "monthDate")));
		criteria.add(Restrictions.le("month", monthDate));
		criteria.add(Restrictions.disjunction().add(Restrictions.isNull("hasAssigned")).add(Restrictions.eq("hasAssigned", false)));
		criteria.add(Restrictions.eq("shop", shop));
		Criteria billsCriteria = criteria.createCriteria("bills");
		billsCriteria.add(Restrictions.eq("status", BillStatus.FULLCHARGE));
		
//		criteria.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
		
		return criteria.list();
	}
	
	public List<String> findShopMonthsAtBeforeMonthOfStateCharged(
			String monthDate, List<Shop> shops) {
		Criteria criteria = this.createCriteria();
		criteria.setProjection(Projections.projectionList().add(Projections.alias(Projections.groupProperty("month"), "monthDate")));
		criteria.add(Restrictions.le("month", monthDate));
		criteria.add(Restrictions.disjunction().add(Restrictions.isNull("hasAssigned")).add(Restrictions.eq("hasAssigned", false)));
		criteria.add(Restrictions.in("shop", shops));
		Criteria billsCriteria = criteria.createCriteria("bills");
		billsCriteria.add(Restrictions.eq("status", BillStatus.FULLCHARGE));
		
//		criteria.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
		
		return criteria.list();
	}
}
